﻿using Dapper;
using System;
using System.Linq;
using System.Text;
using System.Data;
using Ayatta.Contract;
using System.Xml.Linq;
using Ayatta.Service.Extension;
using Ayatta.Contract.Domain;
using System.Collections.Generic;

namespace Ayatta.Service
{
    public class StoreService : BaseService, IStoreService
    {
        #region

        public IList<CategoryGroup> CategoryGroupList(int group)
        {
            var template = new SqlBuilder().Select("*")
                .From(Database.Store.Table.CategoryGroup)
                .Where("[Group]={0}", group)
                .Complete();

            using (var connection = GetConnection(ConnectionName.Store))
            {
                return connection.Query<CategoryGroup>(template.Sql, template.Param).ToList();
            }
        }

        public IList<ProductCategory> ProductCategoryList(int parentId)
        {
            var template = new SqlBuilder().Select("*")
                .From(Database.Store.Table.ProductCategory)
                .Where("[ParentId]={0}", parentId)
                .Complete();

            using (var connection = GetConnection(ConnectionName.Store))
            {
                return connection.Query<ProductCategory>(template.Sql, template.Param).ToList();
            }
        }

        public IList<ProductCategoryProp> ProductCategoryPropList(int categoryId)
        {
            var template = new SqlBuilder().Select("*")
                .From(Database.Store.Table.ProductCategoryProp)
                .Where("[CategoryId]={0}", categoryId)
                .Complete();

            using (var connection = GetConnection(ConnectionName.Store))
            {
                return connection.Query<ProductCategoryProp>(template.Sql, template.Param).ToList();
            }
        }

        public IList<ProductCategoryPropValue> ProductCategoryPropValueList(int categoryId)
        {
            var template = new SqlBuilder().Select("*")
                .From(Database.Store.Table.ProductCategoryPropValue)
                .Where("[CategoryId]={0}", categoryId)
                .Complete();

            using (var connection = GetConnection(ConnectionName.Store))
            {
                return connection.Query<ProductCategoryPropValue>(template.Sql, template.Param).ToList();
            }
        }

        #endregion

        #region Discount

        public bool CreateDiscount(Discount o)
        {
            var builder = new RegexSqlBuilder()
            .Table(Database.Store.Table.Discount)
            .Field("([UserId],[Priority],[Title],[AssignedTo],[UsePercentage],[Percentage],[Amount],[StartTime],[EndTime],[Limitation],[LimitationValue],[Extra],[Status],[ModifiedOn],[CreatedBy],[CreatedOn])VALUES(@UserId,@Priority,@Title,@AssignedTo,@UsePercentage,@Percentage,@Amount,@StartTime,@EndTime,@Limitation,@LimitationValue,@Extra,@Status,@ModifiedOn,@CreatedBy,@CreatedOn)")
            .AddParameters(new { o.UserId, o.Priority, o.Title, o.AssignedTo, o.UsePercentage, o.Percentage, o.Amount, o.StartTime, o.EndTime, o.Limitation, o.LimitationValue, o.Extra, o.Status, o.ModifiedOn, o.CreatedBy, o.CreatedOn });
            var template = builder.Complete(RegexSqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public Discount GetDiscount(int id)
        {
            Discount o;
            var sb = new StringBuilder();
            sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE ID=@Id ORDER BY Priority DESC;", Database.Store.Table.Discount);
            sb.AppendFormat("SELECT * FROM {0} WHERE DiscountId=@Id;", Database.Store.Table.DiscountRequirement);
            var template = new RegexSqlBuilder().Complete(sb.ToString(), new { id });
            using (var connection = GetConnection(ConnectionName.Store))
            using (var multi = connection.QueryMultiple(template.Sql, template.Param))
            {
                o = multi.Read<Discount>().FirstOrDefault();
                if (o != null)
                {
                    o.DiscountRequirements = multi.Read<DiscountRequirement>().ToList();
                }
            }
            return o;
        }

        public PagedList<Discount> GetPagedDiscount(int size = 20, int index = 1, DiscountAssignedTo? assignedTo = null, bool? usePercentage = false, bool? status = false)
        {
            PagedList<Discount> o;
            var builder = new RegexSqlBuilder()
                    .Field("*")
                    .Table(Database.Store.Table.Discount)
                    .OrderBy("id desc");
            if (assignedTo.HasValue)
            {
                builder.Where("[AssignedTo]=@AssignedTo", new { assignedTo });
            }
            if (usePercentage.HasValue)
            {
                builder.Where("[UsePercentage]=@UsePercentage", new { usePercentage });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }
            builder.AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.PagedList<Discount>(template);
            }
        }

        public IList<DiscountRequirement> GetDiscountRequirement(int discountId)
        {
            IList<DiscountRequirement> result;
            using (var connection = GetConnection(ConnectionName.Store))
            {
                result = GetDiscountRequirement(connection, discountId);
            }
            return result;
        }

        internal static SqlTemplate GetDiscountAssignedTo(DiscountAssignedTo assignedTo, int discountId)
        {
            var builder = new RegexSqlBuilder();
            switch (assignedTo)
            {
                case DiscountAssignedTo.Category:
                    builder.Field("[CategoryCode]").Table(
                        Database.Store.Table.DiscountProductCategoryMapping);
                    break;
                case DiscountAssignedTo.Sku:
                    builder.Field("[SkuId]").Table(
                        Database.Store.Table.DiscountItemMap);
                    break;
            }
            builder.Where("[Status]=1 AND [DiscountId]=@DiscountId", new { discountId });
            return builder.Complete(RegexSqlTemplate.Select);
        }

        public bool ValidateDiscount(int discountId, int limitationTimes, int? userId)
        {
            var builder = new RegexSqlBuilder()
                .Field("Count(0) as Count")
                .Table(Database.Store.Table.DiscountUsageHistory)
                .Where("[DiscountId]=@DiscountId", new { discountId });
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[BuyerId]=@UserId", new { userId });
            }

            var template = builder.Complete(RegexSqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return connection.Query<int>(template.Sql, template.Param).First() <= limitationTimes;
            }
        }

        public IList<Discount> GetDiscounts(int userId)
        {
            var tpl = string.Format("SELECT * FROM {0} WHERE [UserId]=@UserId AND [Status]=1 ORDER BY Priority DESC;SELECT * FROM {1} WHERE [UserId]=@UserId AND [Status]=1", Database.Store.Table.Discount, Database.Store.Table.DiscountRequirement);

            var template = new RegexSqlBuilder().Complete(tpl, new { userId });

            using (var connection = GetConnection(ConnectionName.Store))
            {
                return connection.Query<Discount, DiscountRequirement, int>(template, a => a.Id, b => b.DiscountId, (a, b) => { a.DiscountRequirements = b; }).ToList();
            }
        }

        public bool CreateDiscountRequirement(DiscountRequirement o)
        {
            var builder = new RegexSqlBuilder()
             .Table(Database.Store.Table.DiscountRequirement)
             .Field("([DiscountId],[RequirementRule],[SpentAmount],[ShippingAreaIds],[UserRoleIds],[SkuIds],[ItemIds],[CategoryIds][Status],[ModifiedOn],[CreatedBy],[CreatedOn])VALUES(@DiscountId,@RequirementRule,@SpentAmount,@ShippingAreaIds,@UserRoleIds,@SkuIds,@ItemIds,@CategoryIds,@Status,@ModifiedOn,@CreatedBy,@CreatedOn)")
             .AddParameters(new { o.DiscountId, o.RequirementRule, o.SpentAmount, o.ShippingAreaIds, o.UserRoleIds, o.SkuIds, o.ItemIds, o.CategoryIds, o.Status, o.ModifiedOn, o.CreatedBy, o.CreatedOn });

            var template = builder.Complete(RegexSqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Store))
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        private static IList<DiscountRequirement> GetDiscountRequirement(IDbConnection connection, int discountId)
        {
            var builder = new RegexSqlBuilder()
                .Field("*")
                .Table(Database.Store.Table.DiscountRequirement)
                .Where("[DiscountId]=@DiscountId", new { discountId });

            var template = builder.Complete(RegexSqlTemplate.Select);
            return connection.Query<DiscountRequirement>(template.Sql, template.Param).ToList();
        }

        #endregion

        #region ProductItem

        public MagicKit<int> ProductItemCreate(ProductItem o)
        {
            var result = new MagicKit<int>();
            var template = new SqlBuilder()
            .Insert(Database.Store.Table.ProductItem)
            .Values(o.UserId, o.ProductId, o.CategoryId, o.CategoryRootId, o.CategoryMiddleId, o.OuterId, o.BrandId, o.BrandName, o.Name, o.SubName, o.Price, o.Quantity, o.RewardRate, o.PropId, o.PropStr, o.InputId, o.InputStr, o.PropAlias, o.Picture, o.ItemImg, o.PropImg, o.Weight, o.Size, o.Description, o.IsVirtual, o.SubStock, o.AutoFill, o.Showcase, o.IsTiming, o.OnlineTime, o.OfflineTime, o.Relation, o.Sale, o.Collection, o.Consultation, o.Commentation, o.Status, o.CreatedBy, o.CreatedOn)
            .Append("select cast(scope_identity() as int) as Id").Complete();

            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    var id = connection.Query<int>(template.Sql, template.Param, transaction).FirstOrDefault();
                    if (id > 0)
                    {
                        foreach (var sku in o.ProductItemSkus)
                        {
                            template = new SqlBuilder()
                                .Insert(Database.Store.Table.ProductItemSku)
                                .Values(sku.UserId, id, sku.ProductId, sku.CategoryId, sku.CategoryRootId,
                                        sku.CategoryMiddleId, sku.OuterId, sku.PropId, sku.PropStr, sku.Price,
                                        sku.Quantity, sku.Sale, sku.Status, sku.CreatedOn, sku.ModifiedOn)
                                .Complete();
                            connection.Execute(template.Sql, template.Param, transaction);
                        }
                        transaction.Commit();
                        result.Status = true;
                        result.Data = id;
                    }
                    else
                    {
                        transaction.Rollback();
                        result.Message = "添加商品失败！";
                    }
                }
                catch (Exception e)
                {
                    transaction.Rollback();
                    result.Message = e.Message;
                }
            }
            return result;
        }

        public ProductItem ProductItemNameGet(int id)
        {
            var builder = new RegexSqlBuilder()
                 .Field("[Id],[OuterId],[Name],[SubName]")
                 .Table(Database.Store.Table.ProductItem)
                 .Where("[Id] = @Id", new { id });
            var template = builder.Complete(RegexSqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.Query<ProductItem>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public PagedList<ProductItem> ProductItemPagedList(int page = 1, int size = 20, byte param = 0, int? userId = null, int? id = null, string name = null, string outerId = null, int? categoryRootId = null, decimal? priceFrom = null, decimal? priceTo = null, int? saleFrom = null, int? saleTo = null, IDictionary<string, string> orderBy = null)
        {
            var builder = new RegexSqlBuilder().Field("*")
                .Table(Database.Store.Table.ProductItem)
                .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }
            if (param >= 0 && param <= 2)
            {
                builder.Where("[Status]=" + param);
            }
            else if (param == 3)//定时上架
            {
                builder.Where("[IsTiming]=1");
            }
            if (id.HasValue && id.Value > 0)
            {
                builder.Where("[Id]=@Id", new { id });
            }
            if (!string.IsNullOrEmpty(name))
            {
                builder.Where("[Name] LIKE @Name", new { name = name + "%" });
            }
            if (!string.IsNullOrEmpty(outerId))
            {
                builder.Where("[OuterId]=@OuterId", new { outerId });
            }
            if (categoryRootId.HasValue && categoryRootId.Value > 0)
            {
                builder.Where("[CategoryRootId]=@CategoryRootId", new { categoryRootId });
            }
            if (priceFrom.HasValue && priceFrom.Value > 0)
            {
                builder.Where("[Price]>=@PriceFrom", new { priceFrom });
            }
            if (priceTo.HasValue && priceTo.Value > 0)
            {
                builder.Where("[Price]<=@PriceTo", new { priceTo });
            }
            if (saleFrom.HasValue && saleFrom.Value > 0)
            {
                builder.Where("[Sale]>=@SaleFrom", new { saleFrom });
            }
            if (saleTo.HasValue && saleTo.Value > 0)
            {
                builder.Where("[Sale]<=@SaleTo", new { saleTo });
            }
            if (orderBy != null && orderBy.Count > 0)
            {
                var sb = new StringBuilder();
                foreach (var o in orderBy)
                {
                    sb.AppendFormat("[{0}] {1},", o.Key, o.Value);
                }
                builder.OrderBy(sb.ToString().TrimEnd(','));
            }
            else
            {
                builder.OrderBy("[CreatedOn] DESC");
            }
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);

            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.PagedList<ProductItem>(template);
            }
        }

        public bool ProductItemNameUpdate(int id, int userId, string name, string subName)
        {
            var builder = new RegexSqlBuilder()
                .Field("[Name]=@Name", new { name })
                .Field("[SubName]=@SubName", new { subName })
                .Table(Database.Store.Table.ProductItem)
                .Where("[Id] = @Id AND [UserId]=@UserId", new { id, userId });

            var template = builder.Complete(RegexSqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool ProductItemPriceUpdate(int id, int userId, decimal price, IDictionary<int, decimal> prices)
        {
            var xElement = new XElement("xml");
            xElement.Add(new XElement("item",
                                      new XAttribute("Id", id),
                                      new XAttribute("UserId", userId),
                                      new XAttribute("Price", price.ToString("N"))
                             ));
            if (prices != null)
            {
                xElement.Add(new XElement("skus", prices.Select(x => new XElement("sku",
                                                                                  new XAttribute("Id", x.Key),
                                                                                  new XAttribute("UserId", userId),
                                                                                  new XAttribute("Price", x.Value.ToString("N"))
                                                                         ))));
            }
            var xml = xElement.ToString();
            var param = new DynamicParameters();
            param.Add("@xml", xml, DbType.Xml);

            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.Query<bool>("[Proc.Product.Item.Price.Update]", param, commandType: CommandType.StoredProcedure).FirstOrDefault();
            }
        }

        public bool ProductItemQuantityUpdate(int id, int userId, int quantity, IDictionary<int, int> quantities)
        {
            var xElement = new XElement("xml");
            xElement.Add(new XElement("item",
                                      new XAttribute("Id", id),
                                      new XAttribute("UserId", userId),
                                      new XAttribute("Quantity", quantity)
                             ));
            if (quantities != null)
            {
                xElement.Add(new XElement("skus", quantities.Select(x => new XElement("sku",
                                                                                      new XAttribute("Id", x.Key),
                                                                                      new XAttribute("UserId", userId),
                                                                                      new XAttribute("Quantity", x.Value)
                                                                             ))));
            }
            var xml = xElement.ToString();
            var param = new DynamicParameters();
            param.Add("@xml", xml, DbType.Xml);
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.Query<bool>("[Proc.Product.Item.Quantity.Update]", param, commandType: CommandType.StoredProcedure).FirstOrDefault();
            }
        }

        #endregion

        public ProductItemSku GetSku(int id)
        {
            var builder = new RegexSqlBuilder()
                .Field("*")
                .Table(Database.Store.Table.ProductItemSku)
                .Where("[Id]=@Id", new { id });

            var template = builder.Complete(RegexSqlTemplate.Select);

            using (var connection = GetConnection(ConnectionName.Store))
            {
                return connection.Query<ProductItemSku>(template.Sql, template.Param).FirstOrDefault();
            }

        }
        public ProductItem ProductItemGet(int id, int? userId = null)
        {
            var a = new SqlBuilder().Select("Top 1 *").From(Database.Store.Table.ProductItem).Where("[Id]={0}", id)
                .If(userId.HasValue && userId.Value > 0, "[UserId]={0}", userId);
            var b = new SqlBuilder().Select("*").From(Database.Store.Table.ProductItemSku).Where("[ItemId]={0}", id)
                .If(userId.HasValue && userId.Value > 0, "[UserId]={0}", userId);

            var template = new SqlBuilder().Append(a).Append(b).Complete();
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            using (var reader = connection.QueryMultiple(template.Sql, template.Param))
            {
                var o = reader.Read<ProductItem>().FirstOrDefault();
                if (o != null)
                {
                    o.ProductItemSkus = reader.Read<ProductItemSku>().ToList();
                }
                return o;
            }
        }

        public ProductItem ProductItemPriceQuantityGet(int id, int? userId = null)
        {
            var a = new SqlBuilder().Select("Top 1 [Id],[OuterId],[Name],[Price],[Quantity],[Status] ").From(Database.Store.Table.ProductItem).Where("[Id]={0}", id)
                 .If(userId.HasValue && userId.Value > 0, "[UserId]={0}", userId);
            var b = new SqlBuilder().Select("[Id],[ItemId],[OuterId],[Price],[Quantity],[PropStr]").From(Database.Store.Table.ProductItemSku).Where("[ItemId]={0}", id)
                .If(userId.HasValue && userId.Value > 0, "[UserId]={0}", userId);

            var template = new SqlBuilder().Append(a).Append(b).Complete();
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            using (var reader = connection.QueryMultiple(template.Sql, template.Param))
            {
                var o = reader.Read<ProductItem>().FirstOrDefault();
                if (o != null)
                {
                    o.ProductItemSkus = reader.Read<ProductItemSku>().ToList();
                }
                return o;
            }
        }



        #region ProductAuction

        public bool ProductAuctionCreate(ProductAuction o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Store.Table.ProductAuction)
            .Values(o.SkuId, o.ItemId, o.ProductId, o.CategoryId, o.CategoryRootId, o.CategoryMiddleId, o.Level, o.Group, o.Name, o.Price, o.Adjust, o.Interval, o.PriceBuy, o.PriceMarket, o.PriceCost, o.StartTime, o.StopTime, o.AlternateInterval, o.AlternatePrice, o.OnlineDate, o.IsZero, o.IsAuto, o.IsVirtual, o.DiffBuy, o.DiffBuyLimit, o.DiffBuyLimitValue, o.FreeBid, o.FreeBidLimit, o.FreeBidLimitValue, o.Reservation, o.IntegralExchange, o.Picture, o.PropText, o.Description, o.SellerId, o.UserId, o.UserName, o.Nickname, o.CurrentPrice, o.ExpectedTime, o.FinishedOn, o.IpAddress, o.OrderId, o.Extra, o.Status, o.CreatedBy, o.CreatedOn)
            .Complete();
            
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool ProductAuctionUpdate(ProductAuction o)
        {
            var template = new SqlBuilder()
            .Update(Database.Store.Table.ProductAuction)
            .Set("[SkuId]={0}", o.SkuId)
            .Set("[Level]={0}", o.Level)
            .Set("[Group]={0}", o.Group)
            .Set("[Name]={0}", o.Name)
            .Set("[Price]={0}", o.Price)
            .Set("[Adjust]={0}", o.Adjust)
            .Set("[Interval]={0}", o.Interval)
            .Set("[PriceBuy]={0}", o.PriceBuy)
            .Set("[PriceMarket]={0}", o.PriceMarket)
            .Set("[StartTime]={0}", o.StartTime)
            .Set("[StopTime]={0}", o.StopTime)
            .Set("[AlternateInterval]={0}", o.AlternateInterval)
            .Set("[AlternatePrice]={0}", o.AlternatePrice)
            .Set("[OnlineDate]={0}", o.OnlineDate)
            .Set("[IsZero]={0}", o.IsZero)
            .Set("[IsAuto]={0}", o.IsAuto)
            .Set("[IsVirtual]={0}", o.IsVirtual)
            .Set("[DiffBuy]={0}", o.DiffBuy)
            .Set("[DiffBuyLimit]={0}", o.DiffBuyLimit)
            .Set("[DiffBuyLimitValue]={0}", o.DiffBuyLimitValue)
            .Set("[FreeBid]={0}", o.FreeBid)
            .Set("[FreeBidLimit]={0}", o.FreeBidLimit)
            .Set("[FreeBidLimitValue]={0}", o.FreeBidLimitValue)
            .Set("[Reservation]={0}", o.Reservation)
            .Set("[IntegralExchange]={0}", o.IntegralExchange)
            .Set("[Picture]={0}", o.Picture)
            .Set("[PropText]={0}", o.PropText)
            .Set("[Description]={0}", o.Description)
            .Set("[OrderId]={0}", o.OrderId)
            .Set("[Extra]={0}", o.Extra)
            .Set("[Status]={0}", o.Status)
            .Where("[Id]={0} AND [SellerId]={1}", o.Id, o.SellerId).Complete();

            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool ProductAuctionDelete(int id)
        {
            var template = new SqlBuilder()
            .Delete(Database.Store.Table.ProductAuction)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public ProductAuction ProductAuctionGet(int id, int sellerId = 0)
        {
            var template = new SqlBuilder().Select("*")
            .From(Database.Store.Table.ProductAuction)
            .Where("[Id]={0}", id).If(sellerId>0,"[SellerId]={0}",sellerId)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.Query<ProductAuction>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public PagedList<ProductAuction> ProductAuctionPagedList(int page = 1, int size = 20, int id = 0, string name = null, int itemId = 0, int userId = 0, string userName = null, byte? level = null, int? group = null, bool? isZero = null, bool? isAuto = null, bool? isVirtual = null, bool? diffBuy = null, bool? freeBid = null, bool? reservation = null, bool? dynamic = null, AuctionStatus? status = null, DateTime? onlineDate = null, DateTime? finishedDate = null, int sellerId = 0)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Store.Table.ProductAuction)
            .OrderBy("[Id] DESC,[Status]")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            if (id > 0)
            {
                builder.Where("[Id]=@Id", new { id });
            }
            if (!string.IsNullOrEmpty(name))
            {
                builder.Where("[Name] LIKE @Name", new { name = name + "%" });
            }
            if (itemId > 0)
            {
                builder.Where("[ItemId]=@ItemId", new { itemId });
            }
            if (userId > 0)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }
            if (!string.IsNullOrEmpty(userName))
            {
                builder.Where("[UserName]=@UserName", new { userName });
            }
            if (level.HasValue && level.Value > 0)
            {
                builder.Where("[Level]=@Level", new { level });
            }
            if (group > 0)
            {
                builder.Where("[Group]=@Group", new { group });
            }
            if (isZero.HasValue)
            {
                builder.Where("[IsZero]=@IsZero", new { isZero });
            }
            if (isZero.HasValue)
            {
                builder.Where("[IsZero]=@IsZero", new { isZero });
            }
            if (isAuto.HasValue)
            {
                builder.Where("[IsAuto]=@IsAuto", new { isAuto });
            }
            if (isVirtual.HasValue)
            {
                builder.Where("[IsVirtual]=@IsVirtual", new { isVirtual });
            }
            if (diffBuy.HasValue)
            {
                builder.Where("[DiffBuy]=@DiffBuy", new { diffBuy });
            }
            if (freeBid.HasValue)
            {
                builder.Where("[FreeBid]=@FreeBid", new { freeBid });
            }
            if (reservation.HasValue)
            {
                builder.Where("[Reservation]=@Reservation", new { reservation });
            }
            if (dynamic.HasValue)
            {
                builder.Where("[AlternateInterval]<>0");
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }
            if (onlineDate.HasValue)
            {
                builder.Where("[OnlineDate]=@OnlineDate", new { onlineDate });
            }
            if (finishedDate.HasValue)
            {
                var temp = finishedDate.Value;
                var beginDate = finishedDate.Value.Date;
                var endDate = new DateTime(temp.Year, temp.Month, temp.Day).AddDays(1);
                builder.Where("[FinishedOn] IS NOT NULL AND [FinishedOn]>=@BeginDate And [FinishedOn]<@EndDate", new { beginDate, endDate });
            }
            if (sellerId > 0)
            {
                builder.Where("[SellerId]=@SellerId", new { sellerId });
            }
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Store).EnsureOpen())
            {
                return connection.PagedList<ProductAuction>(template);
            }
        }

        #endregion

        #region IService
        public void CachePurge()
        {
            PurgeCache();
        }

        public PagedList<Magic<string, string, int>> CacheGet(int page = 1, int size = 50, int hitCount = 0)
        {
            return GetPagedCache(page, size, hitCount);
        }
        #endregion
    }
}
